/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package procrastinator;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author alex
 */
public class DatabaseConnection {
    private Connection connection;
    private PreparedStatement insertActivity, selectCategoryId, getRecent;
    
    public DatabaseConnection(String dbfilename) throws ClassNotFoundException, SQLException {
        Class.forName("org.apache.derby.jdbc.EmbeddedDriver");

        connectOrCreate(dbfilename);
        
        insertActivity = connection.prepareStatement("INSERT INTO activity (from_time, to_time, category, activity) VALUES (?,?,?,?)");
        selectCategoryId = connection.prepareStatement("SELECT id FROM category WHERE name LIKE ?");
        getRecent = connection.prepareStatement("SELECT * FROM activity, category WHERE activity.category=category.id ORDER BY from_time DESC");
    }

    public List<String> getCategoryNames() {
        List<String> ret = new ArrayList<String>();

        try {
            Statement stmt = connection.createStatement();
            ResultSet rs = stmt.executeQuery("SELECT name FROM category");

            while (rs.next()) {
                ret.add(rs.getString("name"));
            }
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseConnection.class.getName()).log(Level.SEVERE, null, ex);
            ret = null;
        }
        
        return ret;
    }
    
    public Map<String,String> getPatterns() {
        Map<String,String> ret = new HashMap<String, String>();
        
        try {
            Statement stmt = connection.createStatement();
            ResultSet rs = stmt.executeQuery("SELECT pattern,name FROM category,pattern where pattern.category=category.id");

            while (rs.next()) {
                ret.put(rs.getString("pattern"), rs.getString("name"));
            }
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseConnection.class.getName()).log(Level.SEVERE, null, ex);
            ret = null;
        }
        
        return ret;
    }
    
    public int getCategoryId(String categoryName) {
        try {
            selectCategoryId.setString(1, categoryName);
            ResultSet rs = selectCategoryId.executeQuery();

            while (rs.next()) {
                return rs.getInt("id");
            }
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseConnection.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        return -1;
    }
    
    public void addActivity(Date from, Date to, String activity, String category) {
        try {
            int id = getCategoryId(category);
            
            insertActivity.setTimestamp(1, new java.sql.Timestamp(from.getTime()));
            //insertActivity.setTime(2, new java.sql.Time(from.getTime()));
            insertActivity.setTimestamp(2, new java.sql.Timestamp(to.getTime()));
            //insertActivity.setTime(4, new java.sql.Time(to.getTime()));
            insertActivity.setInt(3, id);
            insertActivity.setString(4, activity);
            
            insertActivity.execute();
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseConnection.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
    
    private Activity makeActivityFromRecord(ResultSet rs) throws SQLException {
        return new Activity(new Date(rs.getTimestamp("from_time").getTime()), new Date(rs.getTimestamp("to_time").getTime()), 
                rs.getString("activity"), rs.getString("name"));
    }
    
    public List<Activity> getRecentActivities(int howmany) {
        try {
            List<Activity> ret = new ArrayList<Activity>();
            ResultSet rs = getRecent.executeQuery();
            int count = 0;
            
            while( (count++ < howmany) && rs.next() ) {
                ret.add(makeActivityFromRecord(rs));
            }

            Collections.reverse(ret);
            
            return ret;
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseConnection.class.getName()).log(Level.SEVERE, null, ex);
            return null;
        }
    }

    public void close() {
        try {
            connection.close();
            DriverManager.getConnection("jdbc:derby:;shutdown=true");
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseConnection.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    private void connectOrCreate(String dbfilename) throws SQLException {
        setDBSystemDir();
        
        try {
            connection = DriverManager.getConnection("jdbc:derby:" + dbfilename);
        } catch (SQLException ex) {
            // database didn't exist
            System.err.println("Procrastinator database doesn't exist; I will create a new one.");
            connection = DriverManager.getConnection("jdbc:derby:" + dbfilename + ";create=true");
            
            Statement st = connection.createStatement();
            st.execute(createActivitySql);
            st.execute(createCategorySql);
            st.execute(createPatternSql);
        }
    }

    private void setDBSystemDir() {
        // Decide on the db system directory: <userhome>/.addressbook/
        String userHomeDir = System.getProperty("user.home", ".");
        String systemDir = userHomeDir + "/.procrastinator";

        // Set the db system directory.
        System.setProperty("derby.system.home", systemDir);
    }
    
    
    private static final String createActivitySql = "create table activity (" +
       "id integer not null primary key generated always as identity," +
       "from_time timestamp," +
       "to_time timestamp," +
       "category integer," +
       "activity varchar(500))";
    
    
    private static final String createCategorySql = "create table category (" +
       "id integer not null primary key generated always as identity," +
       "name varchar(500))";
    
    
    private static final String createPatternSql = "create table pattern (" +
       "id integer not null primary key generated always as identity," +
       "pattern varchar(500)," +
       "category integer)";
}
